package org.lc.stk.dao.baseInfo;

import java.util.List;
import java.util.Optional;

import org.lc.stk.model.baseInfo.Cengci;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface CengciRepository extends JpaRepository<Cengci, Integer>, JpaSpecificationExecutor<Cengci> {
    
    /**
     * 根据编码查询培养层次(包括已删除的)
     * @param code 编码
     * @return Optional<Cengci>
     */
    Optional<Cengci> findByCode(String code);

    /**
     * 根据编码查询未删除的培养层次
     * @param code 编码
     * @return Optional<Cengci>
     */
    Optional<Cengci> findByCodeAndIsDeletedFalse(String code);

    /**
     * 根据名称查询未删除的培养层次
     * @param name 名称
     * @return Optional<Cengci>
     */
    Optional<Cengci> findByNameAndIsDeletedFalse(String name);

    /**
     * 根据编码或名称查询未删除的培养层次
     * @param code 编码
     * @param name 名称
     * @return Optional<Cengci>
     */
    @Query("SELECT c FROM Cengci c WHERE c.isDeleted = false AND (c.code = :code OR c.name = :name)")
    Optional<Cengci> findByCodeOrNameAndNotDeleted(@Param("code") String code, @Param("name") String name);

    /**
     * 查询所有未删除的培养层次，按排序号升序排列
     * @return List<Cengci>
     */
    List<Cengci> findByIsDeletedFalseOrderByOrderNumAsc();

    /**
     * 检查编码是否已存在（排除指定ID）
     * @param code 编码
     * @param id ID
     * @return boolean
     */
    @Query("SELECT COUNT(c) > 0 FROM Cengci c WHERE c.code = :code AND c.id != :id AND c.isDeleted = false")
    boolean existsByCodeAndIdNot(@Param("code") String code, @Param("id") Integer id);

    /**
     * 检查名称是否已存在（排除指定ID）
     * @param name 名称
     * @param id ID
     * @return boolean
     */
    @Query("SELECT COUNT(c) > 0 FROM Cengci c WHERE c.name = :name AND c.id != :id AND c.isDeleted = false")
    boolean existsByNameAndIdNot(@Param("name") String name, @Param("id") Integer id);
}